package com.zfskkk.democonsumer;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.HashSet;
import java.util.Set;

/**
 * @author zhangfoshou
 * @date 2018/12/29 14:53
 */
public class ExcelUtil {

    public static void main(String[] args) {
        File file = new File("D:\\Documents\\Downloads");
        File out = new File("D:\\Documents\\Downloads\\out");
        if (!out.exists()) {
            out.mkdirs();
        }
        if (file.exists()) {
            File[] files = file.listFiles();
            for (File f : files) {
                if (f.isFile() && f.getName().contains("xls")) {
                    Set<String> s = readExcel(f, out + File.separator + f.getName());
                    System.out.println(s);
                }
            }
        }
    }

    public static Set<String> readExcel(File excel, String outPutPath) {
        Set<String> name = new HashSet<>();
        //excel文件路径
        try {
            //String encoding = "GBK";
            //判断文件是否存在
            if (excel.isFile() && excel.exists()) {
                //.是特殊字符，需要转义！！！！！
                String[] split = excel.getName().split("\\.");
                Workbook wb;
                //根据文件后缀（xls/xlsx）进行判断
                if ("xls".equals(split[1])) {
                    //文件流对象
                    FileInputStream fis = new FileInputStream(excel);
                    wb = new HSSFWorkbook(fis);
                } else if ("xlsx".equals(split[1])) {
                    wb = new XSSFWorkbook(excel);
                } else {
                    System.out.println("文件类型错误!");
                    return name;
                }

                // 向xls文件中写数据
                FileOutputStream out = new FileOutputStream(outPutPath);

                //开始解析 //读取sheet 0
                Sheet sheet = wb.getSheetAt(0);
                //第一行是列名，
                int firstRowIndex = sheet.getFirstRowNum() + 1;
                int lastRowIndex = sheet.getLastRowNum();
                System.out.println("firstRowIndex: " + firstRowIndex);
                System.out.println("lastRowIndex: " + lastRowIndex);

                BigDecimal available = BigDecimal.ZERO;

                CellStyle style = wb.createCellStyle();
                style.setFillForegroundColor(HSSFColor.DARK_RED.index);


                //遍历行,倒序
                for (int rIndex = lastRowIndex; rIndex >= firstRowIndex; rIndex--) {
//                    System.out.println("rIndex: " + rIndex);
                    Row row = sheet.getRow(rIndex);
                    if (row != null) {
                        int firstCellIndex = row.getFirstCellNum();
                        int lastCellIndex = row.getLastCellNum();
                        // 遍历列
                        for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {
                            Cell cell = row.getCell(cIndex);
                            if (cell != null) {
//                                System.out.print(cell.toString() + "       ");

                                // ################################ //
                                if (cIndex == 3) {
                                    if ("收入".equals(cell.toString())) {
                                        available = available.add(new BigDecimal(row.getCell(cIndex + 1).toString()));
                                    } else if ("支出".equals(cell.toString()) && !"提现".equals(row.getCell(6).toString())) {
                                        available = available.subtract(new BigDecimal(row.getCell(cIndex + 1).toString()));
                                    }
                                    row.createCell(lastCellIndex + 1).setCellStyle(style);
                                    row.getCell(lastCellIndex + 1).setCellValue(available.toString());


                                    if (available.compareTo(new BigDecimal(row.getCell(cIndex + 2).toString())) != 0) {
                                        row.createCell(lastCellIndex + 2).setCellStyle(style);
                                        row.getCell(lastCellIndex + 2).setCellValue("有问题");
                                        name.add(row.getCell(1).toString());
                                    }
                                }

                                // ################################ //

                            }
                        }
                    }
                }

                // 输出
                out.flush();
                wb.write(out);
                out.close();
            } else {
                System.out.println("找不到指定的文件");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }
}
